﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Web;
using System.Web.Configuration;


namespace Rdz.Database
{
	public class OleDb : IDisposable
	{
		public enum ConnectionStringOrigin
		{
			WebConfigAppSettings, WebConfigConnectionSettings, AppConfigAppSettings, Properties
		}

		public enum ConnectorType
		{
			SQLOLEDB_1, SQLNCLI10_1
		}
		OleDbConnection Conn; OleDbCommand Cmd; OleDbDataReader R; OleDbTransaction Trn;
		DataSet DS; OleDbDataAdapter DA;

		string _ConnString = "";
		public string ConnectionString
		{
			get { return _ConnString; }
			set { _ConnString = value; }
		}

		int _RowCount = -1;
		public int RowCount
		{
			get { return _RowCount; }
		}

		public OleDb()
		{
			Conn = new OleDbConnection();
			Conn.ConnectionString = _ConnString;
			Cmd = new OleDbCommand();
			Cmd.Connection = Conn;
			DS = new DataSet();
			DA = new OleDbDataAdapter();
			Conn.Open();
		}

		/// <summary>
		/// Initialize a new OLEDB Database Connection.
		/// </summary>
		/// <param name="Origin">The origin of Connection Strings</param>
		/// <param name="Value">If you use ConnectionStringOrigin.Properties as Origin, the Value is the connection string. Otherwise, is the key name to the settings.</param>
		public OleDb(ConnectionStringOrigin Origin, string Value)
		{
			Conn = new OleDbConnection();
			switch (Origin)
			{
				case ConnectionStringOrigin.Properties:
					Conn.ConnectionString = Value;
					break;
				case ConnectionStringOrigin.WebConfigAppSettings:
					Conn.ConnectionString = WebConfigurationManager.AppSettings[Value];
					break;
				case ConnectionStringOrigin.WebConfigConnectionSettings:
					Conn.ConnectionString = WebConfigurationManager.ConnectionStrings[Value].ConnectionString;
					break;
				case ConnectionStringOrigin.AppConfigAppSettings:
					Conn.ConnectionString = ConfigurationManager.AppSettings[Value];
					break;
			}
			Cmd = new OleDbCommand();
			Cmd.Connection = Conn;
			DS = new DataSet();
			DA = new OleDbDataAdapter();
			Conn.Open();
		}

		/// <summary>
		/// Generate Connection String
		/// </summary>
		/// <param name="ConnType">Specifies the OLEDB Provider to use.</param>
		/// <param name="DatabaseName">Specifies the Database Name to connect.</param>
		/// <returns></returns>
		public static string BuildConnectionString(ConnectorType ConnType, string DatabaseName)
		{
			return BuildConnectionString(ConnType, ".", DatabaseName, "", "");
		}

		/// <summary>
		/// Generate Connection String
		/// </summary>
		/// <param name="ConnType">Specifies the OLEDB Provider to use.</param>
		/// <param name="ServerName">Specifies the Server Name to connect.</param>
		/// <param name="DatabaseName">Specifies the Database Name to connect.</param>
		/// <returns></returns>
		public static string BuildConnectionString(ConnectorType ConnType, string ServerName, string DatabaseName)
		{
			return BuildConnectionString(ConnType, ServerName, DatabaseName, "", "");
		}

		/// <summary>
		/// Generate Connection String
		/// </summary>
		/// <param name="ConnType">Specifies the OLEDB Provider to use.</param>
		/// <param name="ServerName">Specifies the Server Name to connect.</param>
		/// <param name="DatabaseName">Specifies the Database Name to connect.</param>
		/// <param name="SQLUserName">Specifies the SQL Authentication username credential</param>
		/// <param name="SQLPassWord">Specifies the SQL Authentication password credential</param>
		/// <returns></returns>
		public static string BuildConnectionString(ConnectorType ConnType, string ServerName, string DatabaseName, string SQLUserName, string SQLPassWord)
		{
			string ConnString = "";
			if (string.IsNullOrEmpty(SQLUserName))
				ConnString = "Provider=" + ConnType.ToString().Replace("_", ".") +
					";Integrated Security=SSPI;Persist Security Info=False;Data Source=" +
					ServerName + ";Initial Catalog=" + DatabaseName;
			else
				ConnString = "Provider=" + ConnType.ToString().Replace("_", ".") +
					";Persist Security Info=False;Data Source=" +
					ServerName + ";Initial Catalog=" +
					DatabaseName + ";User ID=" +
					SQLUserName + ";Password=" +
					SQLPassWord;
			return ConnString;
		}

		/// <summary>
		/// Get data from Database, returning OleDbDataReader. Using default timeout 240 sec.
		/// </summary>
		/// <param name="QueryString">Specifies the query string to get data.</param>
		/// <returns>OleDbDataReader object.</returns>
		public OleDbDataReader QueryData(string QueryString)
		{
			return QueryData(QueryString, 240);
		}

		/// <summary>
		/// Get data from Database, returning OleDbDataReader.
		/// </summary>
		/// <param name="QueryString">Specifies the query string to get data.</param>
		/// <param name="Timeout">Specifies the timeout to get data.</param>
		/// <returns>OleDbDataReader object.</returns>
		public OleDbDataReader QueryData(string QueryString, int Timeout)
		{
			try
			{
				Cmd.CommandType = CommandType.Text;
				Cmd.CommandTimeout = Timeout;
				Cmd.CommandText = QueryString;
				R = Cmd.ExecuteReader();
				return R;
			} catch (Exception ex)
			{
				throw ex;
			}
		}

		/// <summary>
		/// Get data from Database, returning DataSet. Using default timeout 240 sec.
		/// </summary>
		/// <param name="QueryString">Specifies the query string to get data.</param>
		/// <returns>DataSet object.</returns>
		public DataSet QueryDataSet(string QueryString)
		{
			return QueryDataSet(QueryString, 240);
		}

		/// <summary>
		/// Get data from Database, returning DataSet.
		/// </summary>
		/// <param name="QueryString">Specifies the query string to get data.</param>
		/// <param name="Timeout">Specifies the timeout to get data.</param>
		/// <returns>DataSet object.</returns>
		public DataSet QueryDataSet(string QueryString, int Timeout)
		{
			try
			{
				DS.Clear();
				Cmd.CommandType = CommandType.Text;
				Cmd.CommandTimeout = Timeout;
				Cmd.CommandText = QueryString;
				DA.SelectCommand = Cmd;
				_RowCount = DA.Fill(DS);
				return DS;
			} catch (Exception ex)
			{
				throw ex;
			}
		}

		/// <summary>
		/// Change or Execute non-query SQL command, and no data returned. Using default timeout 240 sec.
		/// </summary>
		/// <param name="QueryString">Specifies the query string to change data / execute command.</param>
		public void ChangeData(string QueryString)
		{
			ChangeData(QueryString, 240);
		}

		/// <summary>
		/// Change or Execute non-query SQL command, and no data returned.
		/// </summary>
		/// <param name="QueryString">Specifies the query string to change data / execute command.</param>
		/// <param name="Timeout">Specifies the timeout to change data / execute command.</param>
		public void ChangeData(string QueryString, int Timeout)
		{
			try
			{
				_RowCount = 0;
				Cmd.CommandType = CommandType.Text;
				Cmd.CommandTimeout = Timeout;
				Cmd.CommandText = QueryString;
				Cmd.ExecuteNonQuery();
				_RowCount++;
			} catch (Exception ex)
			{
				throw ex;
			}
		}

		/// <summary>
		/// Change or Execute collection of non-query SQL commands, and no data returned. Using default timeout 240 sec and using Transaction.
		/// </summary>
		/// <param name="QueryString">Specifies the collection of query strings to change data / execute command.</param>
		public void ChangeData(string[] QueryString)
		{
			ChangeData(QueryString, true, 240);
		}
		/// <summary>
		/// Change or Execute collection of non-query SQL commands, and no data returned. Using Transaction enabled by default.
		/// </summary>
		/// <param name="QueryString">Specifies the collection of query strings to change data / execute command.</param>
		/// <param name="Timeout">Specifies the timeout for every command to change data / execute command.</param>
		public void ChangeData(string[] QueryString, int Timeout)
		{
			ChangeData(QueryString, true, Timeout);
		}
		/// <summary>
		/// Change or Execute collection of non-query SQL commands, and no data returned. Using default timeout 240 sec.
		/// </summary>
		/// <param name="QueryString">Specifies the collection of query strings to change data / execute command.</param>
		/// <param name="EnableTransaction">Specifies whether using Transaction or not.</param>
		public void ChangeData(string[] QueryString, bool EnableTransaction)
		{
			ChangeData(QueryString, EnableTransaction, 240);
		}
		/// <summary>
		/// Change or Execute collection of non-query SQL commands, and no data returned.
		/// </summary>
		/// <param name="QueryString">Specifies the collection of query strings to change data / execute command.</param>
		/// <param name="EnableTransaction">Specifies whether using Transaction or not.</param>
		/// <param name="Timeout">Specifies the timeout for every command to change data / execute command.</param>
		public void ChangeData(string[] QueryString, bool EnableTransaction, int Timeout)
		{
			try
			{
				if (EnableTransaction)
				{
					//if (Trn != null) { Trn.Connection.State = ConnectionSta }
					Trn = Conn.BeginTransaction(IsolationLevel.Serializable);
					Cmd.Transaction = Trn;
				}
				Cmd.CommandType = CommandType.Text;
				Cmd.CommandTimeout = Timeout;
				try
				{
					_RowCount = 0;
					foreach (string Query in QueryString)
					{
						Cmd.CommandText = Query;
						Cmd.ExecuteNonQuery();
						_RowCount++;
					}
					if ((QueryString.Length > 0) && (EnableTransaction))
					{
						Trn.Commit();
					}
				} catch (Exception ex)
				{
					if (EnableTransaction) { Trn.Rollback(); throw ex; }
				}
			} catch (Exception ex)
			{
				throw ex;
			}
		}

		bool disposedValue = false;
		protected virtual void Dispose(bool disposing)
		{
			if (!this.disposedValue)
			{
				if (disposing)
				{
				}
				if (Trn != null) { Trn.Dispose(); }
				if ((R != null) && (!R.IsClosed)) { R.Close(); R.Dispose(); }
				if (Cmd != null) { Cmd.Dispose(); }
				if (DS != null) { DS.Dispose(); }
				if (Conn != null)
				{
					if (Conn.State != ConnectionState.Closed) { Conn.Close(); }
					Conn.Dispose();
				}
				Trn = null; DS = null; Cmd = null; R = null; Conn = null;
			}

			disposedValue = true;
		}
		public void Dispose()
		{
			Dispose(true);
			GC.SuppressFinalize(this);
		}

	}
}
